Imports System.Data.SqlClient
Imports System.Web
Imports System.Configuration
Namespace DALBase

    Public Class cConnessione

        Private conn As SqlConnection
        Dim err As New cException

        Public Function getConnection() As SqlConnection

            If conn.State = ConnectionState.Closed Then conn.Open()
            Return conn

        End Function

        Private Function getConnString() As String

            Dim ConnString As String = CType(HttpContext.Current.Session("ConnString"), String)
            If ConnString = "" Then
                ConnString = ConfigurationSettings.AppSettings("ConnString")
                HttpContext.Current.Session("ConnString") = ConnString
            End If

            Return ConnString
        End Function

        Public Sub New()
            Dim connstring As String
            If IsNothing(conn) Then
                conn = New SqlConnection(getConnString)
            End If
        End Sub


        Public Sub eseguiSql(ByVal sql As String)
            ' crea il l'oggetto OleDbCommand 
            Dim myCmd As SqlCommand = New SqlCommand(sql, conn)

            If myCmd.Connection.State = ConnectionState.Closed Then _
             myCmd.Connection.Open()
            Try
                myCmd.ExecuteNonQuery()

            Catch ex As Exception
                err.get_exception(ex)
            End Try

            myCmd.Connection.Close()

        End Sub

        Public Function getDataTable(ByVal sqlCmd As String) As DataTable
            Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
            Dim table As New DataTable
            myDAdp.SelectCommand = New SqlCommand(sqlCmd, conn)
            myDAdp.SelectCommand.CommandTimeout = 72000
            Try
                myDAdp.Fill(table)
            Catch ex As Exception
                err.get_exception(ex)
            End Try
            Return table
        End Function

        Public Function getDataSet(ByVal sqlCmd As String) As DataSet
            Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
            Dim ds As New DataSet
            myDAdp.SelectCommand = New SqlCommand(sqlCmd, conn)
            myDAdp.SelectCommand.CommandTimeout = 72000
            Try
                myDAdp.Fill(ds)
            Catch ex As Exception
                err.get_exception(ex)
            End Try
            Return ds
        End Function


        Public Function getValore(ByVal sqlCmd As String) As String
            ' crea l'oggetto sqlDbCommand 
            Dim myCmd As SqlCommand = New SqlCommand(sqlCmd, conn)
            Dim val As Object

            If myCmd.Connection.State = ConnectionState.Closed Then _
             myCmd.Connection.Open()
            Try
                val = myCmd.ExecuteScalar()
            Catch ex As Exception
                err.get_exception(ex)
            End Try

            myCmd.Connection.Close()

            If val Is System.DBNull.Value Then
                Return ""
            Else
                Return CStr(val)
            End If


        End Function

        Public Function runSqlCommandNoReturnValue(ByVal oCmd As System.Data.SqlClient.SqlCommand) As String
            Dim sMsg As String = ""
            Try
                oCmd.Connection = conn
                If oCmd.Connection.State <> ConnectionState.Open Then oCmd.Connection.Open()
                oCmd.ExecuteNonQuery()
            Catch exSql As SqlException
                sMsg = "SQLException<BR>" & System.Web.HttpUtility.HtmlEncode(exSql.Message)
            Catch ex As Exception
                sMsg = "Exception<BR>" & System.Web.HttpUtility.HtmlEncode(ex.Message)
            Finally
                oCmd.Connection.Close()
            End Try
            runSqlCommandNoReturnValue = sMsg
        End Function
        Public Function eseguiSPInsert(ByVal nomeSp As String) As String
            Dim idF As String

            Dim insertCMD As SqlCommand = New SqlCommand(nomeSp, conn)
            insertCMD.CommandType = CommandType.StoredProcedure

            Dim par As SqlParameter
            par = insertCMD.Parameters.Add("@id", SqlDbType.Int)
            par.Direction = ParameterDirection.Output

            conn.Open()

            Dim insertReader As SqlDataReader = insertCMD.ExecuteReader()

            idF = insertCMD.Parameters("@id").Value

            insertReader.Close()
            conn.Close()

            Return idF

        End Function

        Public Sub FillDataTable(ByRef tb As System.Data.DataTable, ByVal sSql As String)
            Dim sMsg As String = ""
            Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
            Try
                myDAdp.SelectCommand = New SqlCommand(sSql, conn)
                myDAdp.SelectCommand.CommandTimeout = 72000
                myDAdp.Fill(tb)
            Catch exSql As SqlException
                sMsg = "SQLException<BR>" & System.Web.HttpUtility.HtmlEncode(exSql.Message)
            Catch ex As Exception
                sMsg = "Exception<BR>" & System.Web.HttpUtility.HtmlEncode(ex.Message)
            Finally
                myDAdp.SelectCommand.Connection.Close()
            End Try
        End Sub

        Public Shared Function SQLData(ByVal d As DateTime) As String
            Dim r As String = "CONVERT(DATETIME, '"
            r += d.Year & "-" & d.Month & "-" & d.Day
            r += " 00:00:00', 102)"
            Return r
        End Function
        Public Shared Function SQLString(ByVal og As Object) As String
            Dim r As String = Nothing
            If Not (og Is Nothing) Then
                If Not (og Is System.DBNull.Value) Then
                    r = og.ToString()
                End If
            End If
            Return r
        End Function
    End Class

End Namespace
